<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>10 【创建和管理表】 | Bin</title>
    <meta name="description" content="帝彬">
    <link rel="stylesheet" href="/assets/style.9f8bf5c4.css">
    <link rel="modulepreload" href="/assets/app.82d46cfc.js">
    <link rel="modulepreload" href="/assets/mysql_10.md.33e4e499.lean.js">
    <link rel="prefetch" href="/assets/chunks/VPAlgoliaSearchBox.6ebb7441.js">
    <link rel="icon" href="/logo.svg">
  <link rel="preload" href="/assets/inter-latin.7b37fe23.woff2" as="font" type="font/woff2" crossorigin="anonymous">
  <script>(()=>{const e=localStorage.getItem("vue-theme-appearance");(!e||e==="auto"?window.matchMedia("(prefers-color-scheme: dark)").matches:e==="dark")&&document.documentElement.classList.add("dark")})();</script>
  <meta name="generator" content="qq1974892005">
  <link rel="icon" href="/images/logo.svg">
  <meta name="name" content="Bin">
  <script src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js"></script>
  <script src="https://hm.baidu.com/hm.js?d186385b8ee12dbef7031da2e9c9eee3"></script>
  <script src="/public/tinymce/tinymce.js"></script>
  <meta name="twitter:title" content="10 【创建和管理表】 | Bin">
  <meta property="og:title" content="10 【创建和管理表】 | Bin">
  </head>
  <body>
    <div id="app"><div class="VPApp" data-v-23306c50><!--[--><span tabindex="-1" data-v-3785d3a7></span><a href="#VPContent" class="VPSkipLink visually-hidden" data-v-3785d3a7> Skip to content </a><!--]--><!----><!--[--><div></div><!--]--><header class="VPNav nav-bar" data-v-23306c50 data-v-7d674ffc><div class="VPNavBar" data-v-7d674ffc data-v-96a69ce8><div class="container" data-v-96a69ce8><a class="VPNavBarTitle" href="/" data-v-96a69ce8 data-v-1f5e00a8><!--[--><img class="logo" src="/logo.svg" alt="bin" srcset="" data-v-1f5e00a8><span class="text" data-v-1f5e00a8>Bin</span><!--]--></a><div class="content" data-v-96a69ce8><!----><nav aria-labelledby="main-nav-aria-label" class="VPNavBarMenu menu" data-v-96a69ce8 data-v-9072bcde><span id="main-nav-aria-label" class="visually-hidden" data-v-9072bcde>Main Navigation</span><!--[--><!--[--><a class="vt-link link VPNavBarMenuLink" href="/yuanshen/" data-v-9072bcde data-v-6bdae1a3><!--[-->原神大地图<!--]--><!----></a><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">前端 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/js/01.html"><!--[-->JavaScript<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/ajax/01.html"><!--[-->ajax<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/ES6/01.html"><!--[-->ES6<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/echarts/01.html"><!--[-->echarts<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/typescript/01.html"><!--[-->typescript<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/vue2/01.html"><!--[-->vue2<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/vue3/01.html"><!--[-->vue3<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/git/01.html"><!--[-->git<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">服务端 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/nodejs/01.html"><!--[-->nodejs<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/mysql/01.html"><!--[-->mysql<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/mongodb/01.html"><!--[-->mongodb<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">CSS提升 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/less/01.html"><!--[-->less<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/sass/01.html"><!--[-->sass<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/tailwindcss/01.html"><!--[-->tailwindcss<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">集合 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/React/"><!--[-->React<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/ReactNative/"><!--[-->ReactNative<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vue/"><!--[-->Vue<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vuex/"><!--[-->Vuex<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/jihe/vuerouter/"><!--[-->Vue-Router<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">参数 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/about/canshu.html"><!--[-->常用参数<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/about/dayin.html"><!--[-->打印机<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/about/chahuo.html"><!--[-->市场查货<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="https://sunlogin.oray.com/download" target="_blank" rel="noopener noreferrer"><!--[-->向日葵远程下载<!--]--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" height="24px" viewbox="0 0 24 24" width="24px" class="vt-link-icon"><path d="M0 0h24v24H0V0z" fill="none"></path><path d="M9 5v2h6.59L4 18.59 5.41 20 17 8.41V15h2V5H9z"></path></svg></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--[--><div class="vt-flyout VPNavBarMenuGroup" data-v-9072bcde data-v-42573883><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false"><span class="vt-flyout-button-text">其他 <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-text-icon"><path d="M12,16c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l5.3,5.3l5.3-5.3c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-6,6C12.5,15.9,12.3,16,12,16z"></path></svg></span></button><div class="vt-flyout-menu"><div class="vt-menu"><div class="vt-menu-items"><!--[--><!--[--><a class="vt-link link vt-menu-link" href="/test/"><!--[-->日志<!--]--><!----></a><!--]--><!--[--><a class="vt-link link vt-menu-link" href="/test/muban.html"><!--[-->富文本模板<!--]--><!----></a><!--]--><!--]--></div><!--[--><!--]--></div></div></div><!--]--><!--]--></nav><div class="VPNavBarAppearance appearance" data-v-96a69ce8 data-v-d5f66188><button class="vt-switch vt-switch-appearance" type="button" role="switch" aria-label="toggle dark mode" data-v-d5f66188><span class="vt-switch-check"><span class="vt-switch-icon"><!--[--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-sun"><path d="M12,18c-3.3,0-6-2.7-6-6s2.7-6,6-6s6,2.7,6,6S15.3,18,12,18zM12,8c-2.2,0-4,1.8-4,4c0,2.2,1.8,4,4,4c2.2,0,4-1.8,4-4C16,9.8,14.2,8,12,8z"></path><path d="M12,4c-0.6,0-1-0.4-1-1V1c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,3.6,12.6,4,12,4z"></path><path d="M12,24c-0.6,0-1-0.4-1-1v-2c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,23.6,12.6,24,12,24z"></path><path d="M5.6,6.6c-0.3,0-0.5-0.1-0.7-0.3L3.5,4.9c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C6.2,6.5,5.9,6.6,5.6,6.6z"></path><path d="M19.8,20.8c-0.3,0-0.5-0.1-0.7-0.3l-1.4-1.4c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C20.3,20.7,20,20.8,19.8,20.8z"></path><path d="M3,13H1c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S3.6,13,3,13z"></path><path d="M23,13h-2c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S23.6,13,23,13z"></path><path d="M4.2,20.8c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C4.7,20.7,4.5,20.8,4.2,20.8z"></path><path d="M18.4,6.6c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C18.9,6.5,18.6,6.6,18.4,6.6z"></path></svg><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-moon"><path d="M12.1,22c-0.3,0-0.6,0-0.9,0c-5.5-0.5-9.5-5.4-9-10.9c0.4-4.8,4.2-8.6,9-9c0.4,0,0.8,0.2,1,0.5c0.2,0.3,0.2,0.8-0.1,1.1c-2,2.7-1.4,6.4,1.3,8.4c2.1,1.6,5,1.6,7.1,0c0.3-0.2,0.7-0.3,1.1-0.1c0.3,0.2,0.5,0.6,0.5,1c-0.2,2.7-1.5,5.1-3.6,6.8C16.6,21.2,14.4,22,12.1,22zM9.3,4.4c-2.9,1-5,3.6-5.2,6.8c-0.4,4.4,2.8,8.3,7.2,8.7c2.1,0.2,4.2-0.4,5.8-1.8c1.1-0.9,1.9-2.1,2.4-3.4c-2.5,0.9-5.3,0.5-7.5-1.1C9.2,11.4,8.1,7.7,9.3,4.4z"></path></svg><!--]--></span></span></button></div><div class="vt-social-links VPNavBarSocialLinks social-links" data-v-96a69ce8 data-v-8b9a7f88><!--[--><a class="vt-social-link is-small" href="/translations/" title="languages" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M0 0h24v24H0z" fill="none"></path><path d=" M12.87 15.07l-2.54-2.51.03-.03c1.74-1.94 2.98-4.17 3.71-6.53H17V4h-7V2H8v2H1v1.99h11.17C11.5 7.92 10.44 9.75 9 11.35 8.07 10.32 7.3 9.19 6.69 8h-2c.73 1.63 1.73 3.17 2.98 4.56l-5.09 5.02L4 19l5-5 3.11 3.11.76-2.04zM18.5 10h-2L12 22h2l1.12-3h4.75L21 22h2l-4.5-12zm-2.62 7l1.62-4.33L19.12 17h-3.24z " class="css-c4d79v"></path></svg><span class="visually-hidden">languages</span></a><a class="vt-social-link is-small" href="https://work.weixin.qq.com/kfid/kfc8e47e8eca8390e16" title="github" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg><span class="visually-hidden">github</span></a><a class="vt-social-link is-small" href="mailto:dibinkf@vip.qq.com" title="slack" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M5.042 15.165a2.528 2.528 0 0 1-2.52 2.523A2.528 2.528 0 0 1 0 15.165a2.527 2.527 0 0 1 2.522-2.52h2.52v2.52zM6.313 15.165a2.527 2.527 0 0 1 2.521-2.52 2.527 2.527 0 0 1 2.521 2.52v6.313A2.528 2.528 0 0 1 8.834 24a2.528 2.528 0 0 1-2.521-2.522v-6.313zM8.834 5.042a2.528 2.528 0 0 1-2.521-2.52A2.528 2.528 0 0 1 8.834 0a2.528 2.528 0 0 1 2.521 2.522v2.52H8.834zM8.834 6.313a2.528 2.528 0 0 1 2.521 2.521 2.528 2.528 0 0 1-2.521 2.521H2.522A2.528 2.528 0 0 1 0 8.834a2.528 2.528 0 0 1 2.522-2.521h6.312zM18.956 8.834a2.528 2.528 0 0 1 2.522-2.521A2.528 2.528 0 0 1 24 8.834a2.528 2.528 0 0 1-2.522 2.521h-2.522V8.834zM17.688 8.834a2.528 2.528 0 0 1-2.523 2.521 2.527 2.527 0 0 1-2.52-2.521V2.522A2.527 2.527 0 0 1 15.165 0a2.528 2.528 0 0 1 2.523 2.522v6.312zM15.165 18.956a2.528 2.528 0 0 1 2.523 2.522A2.528 2.528 0 0 1 15.165 24a2.527 2.527 0 0 1-2.52-2.522v-2.522h2.52zM15.165 17.688a2.527 2.527 0 0 1-2.52-2.523 2.526 2.526 0 0 1 2.52-2.52h6.313A2.527 2.527 0 0 1 24 15.165a2.528 2.528 0 0 1-2.522 2.523h-6.313z"></path></svg><span class="visually-hidden">slack</span></a><a class="vt-social-link is-small" href="tencent://message/?uin=1974892005" title="discord" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M20.222 0c1.406 0 2.54 1.137 2.607 2.475V24l-2.677-2.273-1.47-1.338-1.604-1.398.67 2.205H3.71c-1.402 0-2.54-1.065-2.54-2.476V2.48C1.17 1.142 2.31.003 3.715.003h16.5L20.222 0zm-6.118 5.683h-.03l-.202.2c2.073.6 3.076 1.537 3.076 1.537-1.336-.668-2.54-1.002-3.744-1.137-.87-.135-1.74-.064-2.475 0h-.2c-.47 0-1.47.2-2.81.735-.467.203-.735.336-.735.336s1.002-1.002 3.21-1.537l-.135-.135s-1.672-.064-3.477 1.27c0 0-1.805 3.144-1.805 7.02 0 0 1 1.74 3.743 1.806 0 0 .4-.533.805-1.002-1.54-.468-2.14-1.404-2.14-1.404s.134.066.335.2h.06c.03 0 .044.015.06.03v.006c.016.016.03.03.06.03.33.136.66.27.93.4.466.202 1.065.403 1.8.536.93.135 1.996.2 3.21 0 .6-.135 1.2-.267 1.8-.535.39-.2.87-.4 1.397-.737 0 0-.6.936-2.205 1.404.33.466.795 1 .795 1 2.744-.06 3.81-1.8 3.87-1.726 0-3.87-1.815-7.02-1.815-7.02-1.635-1.214-3.165-1.26-3.435-1.26l.056-.02zm.168 4.413c.703 0 1.27.6 1.27 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334.002-.74.573-1.338 1.27-1.338zm-4.543 0c.7 0 1.266.6 1.266 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334 0-.74.57-1.338 1.27-1.338z"></path></svg><span class="visually-hidden">discord</span></a><!--]--></div><div class="vt-flyout VPNavBarExtra extra" data-v-96a69ce8 data-v-b3e218c4><button type="button" class="vt-flyout-button" aria-haspopup="true" aria-expanded="false" aria-label="extra navigation"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-flyout-button-icon"><circle cx="12" cy="12" r="2"></circle><circle cx="19" cy="12" r="2"></circle><circle cx="5" cy="12" r="2"></circle></svg></button><div class="vt-flyout-menu"><div class="vt-menu"><!----><!--[--><!--[--><div class="vt-menu-group" data-v-b3e218c4><div class="vt-menu-item item" data-v-b3e218c4><p class="vt-menu-label" data-v-b3e218c4>Appearance</p><div class="vt-menu-action action" data-v-b3e218c4><button class="vt-switch vt-switch-appearance" type="button" role="switch" aria-label="toggle dark mode" data-v-b3e218c4><span class="vt-switch-check"><span class="vt-switch-icon"><!--[--><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-sun"><path d="M12,18c-3.3,0-6-2.7-6-6s2.7-6,6-6s6,2.7,6,6S15.3,18,12,18zM12,8c-2.2,0-4,1.8-4,4c0,2.2,1.8,4,4,4c2.2,0,4-1.8,4-4C16,9.8,14.2,8,12,8z"></path><path d="M12,4c-0.6,0-1-0.4-1-1V1c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,3.6,12.6,4,12,4z"></path><path d="M12,24c-0.6,0-1-0.4-1-1v-2c0-0.6,0.4-1,1-1s1,0.4,1,1v2C13,23.6,12.6,24,12,24z"></path><path d="M5.6,6.6c-0.3,0-0.5-0.1-0.7-0.3L3.5,4.9c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C6.2,6.5,5.9,6.6,5.6,6.6z"></path><path d="M19.8,20.8c-0.3,0-0.5-0.1-0.7-0.3l-1.4-1.4c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l1.4,1.4c0.4,0.4,0.4,1,0,1.4C20.3,20.7,20,20.8,19.8,20.8z"></path><path d="M3,13H1c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S3.6,13,3,13z"></path><path d="M23,13h-2c-0.6,0-1-0.4-1-1s0.4-1,1-1h2c0.6,0,1,0.4,1,1S23.6,13,23,13z"></path><path d="M4.2,20.8c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C4.7,20.7,4.5,20.8,4.2,20.8z"></path><path d="M18.4,6.6c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l1.4-1.4c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4l-1.4,1.4C18.9,6.5,18.6,6.6,18.4,6.6z"></path></svg><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-switch-appearance-moon"><path d="M12.1,22c-0.3,0-0.6,0-0.9,0c-5.5-0.5-9.5-5.4-9-10.9c0.4-4.8,4.2-8.6,9-9c0.4,0,0.8,0.2,1,0.5c0.2,0.3,0.2,0.8-0.1,1.1c-2,2.7-1.4,6.4,1.3,8.4c2.1,1.6,5,1.6,7.1,0c0.3-0.2,0.7-0.3,1.1-0.1c0.3,0.2,0.5,0.6,0.5,1c-0.2,2.7-1.5,5.1-3.6,6.8C16.6,21.2,14.4,22,12.1,22zM9.3,4.4c-2.9,1-5,3.6-5.2,6.8c-0.4,4.4,2.8,8.3,7.2,8.7c2.1,0.2,4.2-0.4,5.8-1.8c1.1-0.9,1.9-2.1,2.4-3.4c-2.5,0.9-5.3,0.5-7.5-1.1C9.2,11.4,8.1,7.7,9.3,4.4z"></path></svg><!--]--></span></span></button></div></div></div><div class="vt-menu-group" data-v-b3e218c4><div class="vt-menu-item item" data-v-b3e218c4><div class="vt-social-links social-links" data-v-b3e218c4><!--[--><a class="vt-social-link is-small" href="/translations/" title="languages" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M0 0h24v24H0z" fill="none"></path><path d=" M12.87 15.07l-2.54-2.51.03-.03c1.74-1.94 2.98-4.17 3.71-6.53H17V4h-7V2H8v2H1v1.99h11.17C11.5 7.92 10.44 9.75 9 11.35 8.07 10.32 7.3 9.19 6.69 8h-2c.73 1.63 1.73 3.17 2.98 4.56l-5.09 5.02L4 19l5-5 3.11 3.11.76-2.04zM18.5 10h-2L12 22h2l1.12-3h4.75L21 22h2l-4.5-12zm-2.62 7l1.62-4.33L19.12 17h-3.24z " class="css-c4d79v"></path></svg><span class="visually-hidden">languages</span></a><a class="vt-social-link is-small" href="https://work.weixin.qq.com/kfid/kfc8e47e8eca8390e16" title="github" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M12 .297c-6.63 0-12 5.373-12 12 0 5.303 3.438 9.8 8.205 11.385.6.113.82-.258.82-.577 0-.285-.01-1.04-.015-2.04-3.338.724-4.042-1.61-4.042-1.61C4.422 18.07 3.633 17.7 3.633 17.7c-1.087-.744.084-.729.084-.729 1.205.084 1.838 1.236 1.838 1.236 1.07 1.835 2.809 1.305 3.495.998.108-.776.417-1.305.76-1.605-2.665-.3-5.466-1.332-5.466-5.93 0-1.31.465-2.38 1.235-3.22-.135-.303-.54-1.523.105-3.176 0 0 1.005-.322 3.3 1.23.96-.267 1.98-.399 3-.405 1.02.006 2.04.138 3 .405 2.28-1.552 3.285-1.23 3.285-1.23.645 1.653.24 2.873.12 3.176.765.84 1.23 1.91 1.23 3.22 0 4.61-2.805 5.625-5.475 5.92.42.36.81 1.096.81 2.22 0 1.606-.015 2.896-.015 3.286 0 .315.21.69.825.57C20.565 22.092 24 17.592 24 12.297c0-6.627-5.373-12-12-12"></path></svg><span class="visually-hidden">github</span></a><a class="vt-social-link is-small" href="mailto:dibinkf@vip.qq.com" title="slack" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M5.042 15.165a2.528 2.528 0 0 1-2.52 2.523A2.528 2.528 0 0 1 0 15.165a2.527 2.527 0 0 1 2.522-2.52h2.52v2.52zM6.313 15.165a2.527 2.527 0 0 1 2.521-2.52 2.527 2.527 0 0 1 2.521 2.52v6.313A2.528 2.528 0 0 1 8.834 24a2.528 2.528 0 0 1-2.521-2.522v-6.313zM8.834 5.042a2.528 2.528 0 0 1-2.521-2.52A2.528 2.528 0 0 1 8.834 0a2.528 2.528 0 0 1 2.521 2.522v2.52H8.834zM8.834 6.313a2.528 2.528 0 0 1 2.521 2.521 2.528 2.528 0 0 1-2.521 2.521H2.522A2.528 2.528 0 0 1 0 8.834a2.528 2.528 0 0 1 2.522-2.521h6.312zM18.956 8.834a2.528 2.528 0 0 1 2.522-2.521A2.528 2.528 0 0 1 24 8.834a2.528 2.528 0 0 1-2.522 2.521h-2.522V8.834zM17.688 8.834a2.528 2.528 0 0 1-2.523 2.521 2.527 2.527 0 0 1-2.52-2.521V2.522A2.527 2.527 0 0 1 15.165 0a2.528 2.528 0 0 1 2.523 2.522v6.312zM15.165 18.956a2.528 2.528 0 0 1 2.523 2.522A2.528 2.528 0 0 1 15.165 24a2.527 2.527 0 0 1-2.52-2.522v-2.522h2.52zM15.165 17.688a2.527 2.527 0 0 1-2.52-2.523 2.526 2.526 0 0 1 2.52-2.52h6.313A2.527 2.527 0 0 1 24 15.165a2.528 2.528 0 0 1-2.522 2.523h-6.313z"></path></svg><span class="visually-hidden">slack</span></a><a class="vt-social-link is-small" href="tencent://message/?uin=1974892005" title="discord" target="_blank" rel="noopener noreferrer"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-social-link-icon"><path d="M20.222 0c1.406 0 2.54 1.137 2.607 2.475V24l-2.677-2.273-1.47-1.338-1.604-1.398.67 2.205H3.71c-1.402 0-2.54-1.065-2.54-2.476V2.48C1.17 1.142 2.31.003 3.715.003h16.5L20.222 0zm-6.118 5.683h-.03l-.202.2c2.073.6 3.076 1.537 3.076 1.537-1.336-.668-2.54-1.002-3.744-1.137-.87-.135-1.74-.064-2.475 0h-.2c-.47 0-1.47.2-2.81.735-.467.203-.735.336-.735.336s1.002-1.002 3.21-1.537l-.135-.135s-1.672-.064-3.477 1.27c0 0-1.805 3.144-1.805 7.02 0 0 1 1.74 3.743 1.806 0 0 .4-.533.805-1.002-1.54-.468-2.14-1.404-2.14-1.404s.134.066.335.2h.06c.03 0 .044.015.06.03v.006c.016.016.03.03.06.03.33.136.66.27.93.4.466.202 1.065.403 1.8.536.93.135 1.996.2 3.21 0 .6-.135 1.2-.267 1.8-.535.39-.2.87-.4 1.397-.737 0 0-.6.936-2.205 1.404.33.466.795 1 .795 1 2.744-.06 3.81-1.8 3.87-1.726 0-3.87-1.815-7.02-1.815-7.02-1.635-1.214-3.165-1.26-3.435-1.26l.056-.02zm.168 4.413c.703 0 1.27.6 1.27 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334.002-.74.573-1.338 1.27-1.338zm-4.543 0c.7 0 1.266.6 1.266 1.335 0 .74-.57 1.34-1.27 1.34-.7 0-1.27-.6-1.27-1.334 0-.74.57-1.338 1.27-1.338z"></path></svg><span class="visually-hidden">discord</span></a><!--]--></div></div></div><!--]--><!--]--></div></div></div><button type="button" class="vt-hamburger VPNavBarHamburger hamburger" aria-label="mobile navigation" aria-expanded="false" aria-controls="VPNavScreen" data-v-96a69ce8 data-v-c78f86de><span class="vt-hamburger-container"><span class="vt-hamburger-top"></span><span class="vt-hamburger-middle"></span><span class="vt-hamburger-bottom"></span></span></button></div></div></div><!----></header><div class="VPLocalNav" data-v-23306c50 data-v-7cd5ed0a><button class="menu" aria-expanded="false" aria-controls="VPSidebarNav" data-v-7cd5ed0a><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="menu-icon" data-v-7cd5ed0a><path d="M17,11H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h14c0.6,0,1,0.4,1,1S17.6,11,17,11z"></path><path d="M21,7H3C2.4,7,2,6.6,2,6s0.4-1,1-1h18c0.6,0,1,0.4,1,1S21.6,7,21,7z"></path><path d="M21,15H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h18c0.6,0,1,0.4,1,1S21.6,15,21,15z"></path><path d="M17,19H3c-0.6,0-1-0.4-1-1s0.4-1,1-1h14c0.6,0,1,0.4,1,1S17.6,19,17,19z"></path></svg><span class="menu-text" data-v-7cd5ed0a>Menu</span></button><a class="top-link" href="#" data-v-7cd5ed0a>Return to top</a></div><aside class="VPSidebar" data-v-23306c50 data-v-22052bdb><nav id="VPSidebarNav" aria-labelledby="sidebar-aria-label" tabindex="-1" data-v-22052bdb><!--[--><!--]--><span id="sidebar-aria-label" class="visually-hidden" data-v-22052bdb>Sidebar Navigation</span><!--[--><div class="group" data-v-22052bdb><section class="VPSidebarGroup" data-v-22052bdb data-v-59eaa146><div class="title" data-v-59eaa146><h2 class="active title-text" data-v-59eaa146>mysql</h2></div><!--[--><a class="link" href="/mysql/01.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>01 【数据库概述】</p></a><a class="link" href="/mysql/02.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>02 【MySQL基本使用】</p></a><a class="link" href="/mysql/03.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>03 【基本的SELECT语句】</p></a><a class="link" href="/mysql/04.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>04 【运算符】</p></a><a class="link" href="/mysql/05.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>05 【排序与分页】</p></a><a class="link" href="/mysql/06.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>06 【多表查询】</p></a><a class="link" href="/mysql/07.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>07 【单行函数】</p></a><a class="link" href="/mysql/08.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>08 【聚合函数与分组查询】</p></a><a class="link" href="/mysql/09.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>09 【子查询】</p></a><a class="link active" href="/mysql/10.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>10 【创建和管理表】</p></a><a class="link" href="/mysql/11.html" data-v-59eaa146 data-v-531573d4><p class="link-text" data-v-531573d4>11 【数据处理之增删改】</p></a><!--]--></section></div><!--]--><!--[--><!--]--></nav></aside><div id="VPContent" class="VPContent has-sidebar" data-v-23306c50 data-v-790151bc><div class="VPContentDoc has-aside has-sidebar" data-v-790151bc data-v-4fe9b7bd><div class="container" data-v-4fe9b7bd><div class="aside" data-v-4fe9b7bd><div class="aside-container" data-v-4fe9b7bd><!--[--><!--]--><div class="VPContentDocOutline" data-v-4fe9b7bd data-v-aa0e2252><div class="outline-marker" data-v-aa0e2252></div><div class="outline-title" data-v-aa0e2252>On this page</div><nav aria-labelledby="doc-outline-aria-label" data-v-aa0e2252><span id="doc-outline-aria-label" class="visually-hidden" data-v-aa0e2252>Table of Contents for current page</span><ul class="root" data-v-aa0e2252><!--[--><li style="" data-v-aa0e2252><a class="outline-link" href="#_1-基础知识" data-v-aa0e2252>1. 基础知识</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_2-创建和管理数据库" data-v-aa0e2252>2. 创建和管理数据库</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_3-创建表" data-v-aa0e2252>3. 创建表</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_4-修改表" data-v-aa0e2252>4. 修改表</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_5-重命名表" data-v-aa0e2252>5. 重命名表</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_6-删除表" data-v-aa0e2252>6. 删除表</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_7-清空表" data-v-aa0e2252>7. 清空表</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_8-dcl-中-commit-和-rollback" data-v-aa0e2252>8.DCL 中 COMMIT 和 ROLLBACK</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_9-对比-truncate-table-和-delete-from" data-v-aa0e2252>9.对比 TRUNCATE TABLE 和 DELETE FROM</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#_10-内容拓展" data-v-aa0e2252>10. 内容拓展</a><!----></li><li style="" data-v-aa0e2252><a class="outline-link" href="#练习" data-v-aa0e2252>练习</a><!----></li><!--]--></ul></nav></div><!--[--><!--[--><!--[--><div><a class="sponsors-aside-text" href="/">联系客服</a><img src="/images/qrcode.png"></div><!--]--><!--]--><!--]--><div class="VPCarbonAds" data-v-4fe9b7bd><img src="/logo.svg" alt="" srcset=""></div><!--[--><!--[--><!--[--><h1 class="tagline" data-v-3916b2f0><span class="accent" data-v-3916b2f0>Bin</span><br data-v-3916b2f0>QQ - 1974892005 </h1><!--]--><!--]--><!--]--></div></div><div class="content" data-v-4fe9b7bd><!--[--><!--]--><main data-v-4fe9b7bd><div style="position:relative;" class="vt-doc mysql" data-v-4fe9b7bd><div><h1 id="_10-【创建和管理表】" tabindex="-1">10 【创建和管理表】 <a class="header-anchor" href="#_10-【创建和管理表】" aria-hidden="true">#</a></h1><h2 id="_1-基础知识" tabindex="-1">1. 基础知识 <a class="header-anchor" href="#_1-基础知识" aria-hidden="true">#</a></h2><h3 id="_1-1-一条数据存储的过程" tabindex="-1">1.1 一条数据存储的过程 <a class="header-anchor" href="#_1-1-一条数据存储的过程" aria-hidden="true">#</a></h3><p><code>存储数据是处理数据的第一步</code>。只有正确地把数据存储起来，我们才能进行有效的处理和分析。否则，只能是一团乱麻，无从下手。</p><p>那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。</p><p><img src="https://i0.hdslb.com/bfs/album/c96a21089d33165b5ab9876aa51f4d9c421afa4d.png" alt=""></p><p>我们要先创建一个数据库，而不是直接创建数据表呢？</p><p>因为从系统架构的层次上看，MySQL 数据库系统从大到小依次是<code>数据库服务器</code>、<code>数据库</code>、<code>数据表</code>、数据表的<code>行与列</code>。</p><p>MySQL 数据库服务器之前已经安装。所以，我们就从创建数据库开始。</p><h3 id="_1-2-标识符命名规则" tabindex="-1">1.2 标识符命名规则 <a class="header-anchor" href="#_1-2-标识符命名规则" aria-hidden="true">#</a></h3><ul><li>数据库名、表名不得超过30个字符，变量名限制为29个</li><li>必须只能包含 A–Z, a–z, 0–9, _共63个字符</li><li>数据库名、表名、字段名等对象名中间不要包含空格</li><li>同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名</li><li>必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来</li><li>保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了</li></ul><h3 id="_1-3-mysql中的数据类型" tabindex="-1">1.3 MySQL中的数据类型 <a class="header-anchor" href="#_1-3-mysql中的数据类型" aria-hidden="true">#</a></h3><table><thead><tr><th>类型</th><th>类型举例</th></tr></thead><tbody><tr><td>整数类型</td><td>TINYINT、SMALLINT、MEDIUMINT、<strong>INT(或INTEGER)</strong>、BIGINT</td></tr><tr><td>浮点类型</td><td>FLOAT、DOUBLE</td></tr><tr><td>定点数类型</td><td><strong>DECIMAL</strong></td></tr><tr><td>位类型</td><td>BIT</td></tr><tr><td>日期时间类型</td><td>YEAR、TIME、<strong>DATE</strong>、DATETIME、TIMESTAMP</td></tr><tr><td>文本字符串类型</td><td>CHAR、<strong>VARCHAR</strong>、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT</td></tr><tr><td>枚举类型</td><td>ENUM</td></tr><tr><td>集合类型</td><td>SET</td></tr><tr><td>二进制字符串类型</td><td>BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB</td></tr></tbody></table><p>其中，常用的几类类型介绍如下：</p><table><thead><tr><th>数据类型</th><th>描述</th></tr></thead><tbody><tr><td>INT</td><td>从-2^31到 2^31-1的整型数据。<strong><code>存储大小为 4个字节</code></strong></td></tr><tr><td>CHAR(size)</td><td>定长字符数据。若未指定，默认为1个字符，最大长度255</td></tr><tr><td>VARCHAR(size)</td><td>可变长字符数据，根据字符串实际长度保存，<strong><code>必须指定长度</code></strong></td></tr><tr><td>FLOAT(M,D)</td><td>单精度，占用4个字节，M=整数位+小数位，D=小数位。 D&lt;=M&lt;=255,0&lt;=D&lt;=30，默认M+D&lt;=6</td></tr><tr><td>DOUBLE(M,D)</td><td>双精度，占用8个字节，D&lt;=M&lt;=255,0&lt;=D&lt;=30，默认M+D&lt;=15</td></tr><tr><td>DECIMAL(M,D)</td><td>高精度小数，占用M+2个字节，D&lt;=M&lt;=65，0&lt;=D&lt;=30，最大取值范围与DOUBLE相同。</td></tr><tr><td>DATE</td><td>日期型数据，格式&#39;YYYY-MM-DD&#39;</td></tr><tr><td>BLOB</td><td>二进制形式的长文本数据，最大可达4G</td></tr><tr><td>TEXT</td><td>长文本数据，最大可达4G</td></tr></tbody></table><h2 id="_2-创建和管理数据库" tabindex="-1">2. 创建和管理数据库 <a class="header-anchor" href="#_2-创建和管理数据库" aria-hidden="true">#</a></h2><h3 id="_2-1-创建数据库" tabindex="-1">2.1 创建数据库 <a class="header-anchor" href="#_2-1-创建数据库" aria-hidden="true">#</a></h3><ul><li>方式1：创建数据库</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> 数据库名; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>方式2：创建数据库并指定字符集</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> 数据库名 </span><span style="color:#F78C6C;">CHARACTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SET</span><span style="color:#A6ACCD;"> 字符集;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>方式3：判断数据库是否已经存在，不存在则创建数据库（<code>推荐</code>）</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IF</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">EXISTS</span><span style="color:#A6ACCD;"> 数据库名; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。</p><blockquote><p>注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。</p></blockquote><h3 id="_2-2-使用数据库" tabindex="-1">2.2 使用数据库 <a class="header-anchor" href="#_2-2-使用数据库" aria-hidden="true">#</a></h3><ul><li>查看当前所有的数据库</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">SHOW DATABASES; #有一个S，代表多个数据库</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>查看当前正在使用的数据库</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#89DDFF;">()</span><span style="color:#A6ACCD;">;  #使用的一个 mysql 中的全局函数</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>查看指定库下所有的表</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">SHOW TABLES </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> 数据库名;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>查看数据库的创建信息</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">SHOW </span><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> 数据库名;</span></span>
<span class="line"><span style="color:#A6ACCD;">或者：</span></span>
<span class="line"><span style="color:#A6ACCD;">SHOW </span><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> 数据库名\G</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li>使用/切换数据库</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">USE</span><span style="color:#A6ACCD;"> 数据库名;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><blockquote><p>注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。</p></blockquote><h3 id="_2-3-修改数据库" tabindex="-1">2.3 修改数据库 <a class="header-anchor" href="#_2-3-修改数据库" aria-hidden="true">#</a></h3><ul><li>更改数据库字符集</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> 数据库名 </span><span style="color:#F78C6C;">CHARACTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SET</span><span style="color:#A6ACCD;"> 字符集;  #比如：gbk、utf8等</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="_2-4-删除数据库" tabindex="-1">2.4 删除数据库 <a class="header-anchor" href="#_2-4-删除数据库" aria-hidden="true">#</a></h3><ul><li>方式1：删除指定的数据库</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> 数据库名;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>方式2：删除指定的数据库（<code>推荐</code>）</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IF</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">EXISTS</span><span style="color:#A6ACCD;"> 数据库名;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="_3-创建表" tabindex="-1">3. 创建表 <a class="header-anchor" href="#_3-创建表" aria-hidden="true">#</a></h2><h3 id="_3-1-创建方式1" tabindex="-1">3.1 创建方式1 <a class="header-anchor" href="#_3-1-创建方式1" aria-hidden="true">#</a></h3><ul><li>必须具备： <ul><li>CREATE TABLE权限</li><li>存储空间</li></ul></li><li><strong>语法格式：</strong></li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> [IF NOT EXISTS] 表名(</span></span>
<span class="line"><span style="color:#A6ACCD;">	字段1, 数据类型 [约束条件] [默认值],</span></span>
<span class="line"><span style="color:#A6ACCD;">	字段2, 数据类型 [约束条件] [默认值],</span></span>
<span class="line"><span style="color:#A6ACCD;">	字段3, 数据类型 [约束条件] [默认值],</span></span>
<span class="line"><span style="color:#A6ACCD;">	……</span></span>
<span class="line"><span style="color:#A6ACCD;">	[表约束条件]</span></span>
<span class="line"><span style="color:#A6ACCD;">);</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><blockquote><p>加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。</p><p>如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的的字符集</p></blockquote><ul><li>必须指定： <ul><li>表名</li><li>列名(或字段名)，数据类型，<strong>长度</strong></li></ul></li><li>可选指定： <ul><li>约束条件</li><li>默认值</li></ul></li><li>创建表举例1：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">-- 创建表</span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp (</span></span>
<span class="line"><span style="color:#89DDFF;">  </span><span style="color:#676E95;font-style:italic;">-- int类型</span></span>
<span class="line"><span style="color:#A6ACCD;">  emp_id </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">,</span></span>
<span class="line"><span style="color:#89DDFF;">  </span><span style="color:#676E95;font-style:italic;">-- 最多保存20个中英文字符</span></span>
<span class="line"><span style="color:#A6ACCD;">  emp_name </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">20</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#89DDFF;">  </span><span style="color:#676E95;font-style:italic;">-- 总位数不超过15位</span></span>
<span class="line"><span style="color:#A6ACCD;">  salary DOUBLE,</span></span>
<span class="line"><span style="color:#89DDFF;">  </span><span style="color:#676E95;font-style:italic;">-- 日期类型</span></span>
<span class="line"><span style="color:#A6ACCD;">  birthday </span><span style="color:#F78C6C;">DATE</span></span>
<span class="line"><span style="color:#A6ACCD;">);</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#F78C6C;">DESC</span><span style="color:#A6ACCD;"> emp;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><img src="https://i0.hdslb.com/bfs/album/f00b624928efaf9f2cc0eb9d0f38428c07c1660f.png" alt="image-20221021203136254" style="zoom:150%;"><p>MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。</p><ul><li>创建表举例2：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> dept(</span></span>
<span class="line"><span style="color:#89DDFF;">    </span><span style="color:#676E95;font-style:italic;">-- int类型，自增</span></span>
<span class="line"><span style="color:#A6ACCD;">	deptno </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;">) AUTO_INCREMENT,</span></span>
<span class="line"><span style="color:#A6ACCD;">	dname </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">14</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">	loc </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">13</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#89DDFF;">    </span><span style="color:#676E95;font-style:italic;">-- 主键</span></span>
<span class="line"><span style="color:#A6ACCD;">    </span><span style="color:#F78C6C;">PRIMARY</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">KEY</span><span style="color:#A6ACCD;"> (deptno)</span></span>
<span class="line"><span style="color:#A6ACCD;">);</span></span>
<span class="line"></span>
<span class="line"></span>
<span class="line"><span style="color:#A6ACCD;">DESCRIBE dept;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/30f6e7b1119fcd3c0d30d51064930055b2869533.png" alt=""></p><blockquote><p>在MySQL 8.x版本中，不再推荐为INT类型指定显示长度，并在未来的版本中可能去掉这样的语法。</p></blockquote><h3 id="_3-2-创建方式2" tabindex="-1">3.2 创建方式2 <a class="header-anchor" href="#_3-2-创建方式2" aria-hidden="true">#</a></h3><ul><li>使用 AS subquery 选项，<strong>将创建表和插入数据结合起来</strong></li></ul><p><img src="https://i0.hdslb.com/bfs/album/04bd029c72c3ce75a476675ffea99a40abf2e3ec.png" alt=""></p><ul><li>指定的列和子查询中的列要一一对应</li><li>通过列名和默认值定义列</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp1 </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees;  </span><span style="color:#676E95;font-style:italic;">-- 创建emp1表，并把employees表中的数据全部复制给emp1表</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp2 </span><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> employees </span><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1</span><span style="color:#89DDFF;">=</span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;">; </span><span style="color:#676E95;font-style:italic;">-- 创建的emp2是空表</span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> dept80</span></span>
<span class="line"><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;">  employee_id emp_id, last_name </span><span style="color:#F78C6C;">name</span><span style="color:#A6ACCD;">, salary</span><span style="color:#89DDFF;">*</span><span style="color:#F78C6C;">12</span><span style="color:#A6ACCD;"> ANNSAL, hire_date   </span><span style="color:#676E95;font-style:italic;">-- 别名充当新创建表的字段名</span></span>
<span class="line"><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;">    employees</span></span>
<span class="line"><span style="color:#F78C6C;">WHERE</span><span style="color:#A6ACCD;">   department_id </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">80</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">DESCRIBE dept80;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/4e54f8224e3fd3f881179796500a6b6510303d1c.png" alt=""></p><h3 id="_3-3-查看数据表结构" tabindex="-1">3.3 查看数据表结构 <a class="header-anchor" href="#_3-3-查看数据表结构" aria-hidden="true">#</a></h3><p>在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用<code>DESCRIBE/DESC</code>语句查看数据表结构，也支持使用<code>SHOW CREATE TABLE</code>语句查看数据表结构。</p><p>语法格式如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">-- 查看创建数据表的结构</span></span>
<span class="line"><span style="color:#A6ACCD;">SHOW </span><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> 表名\G  </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。</p><h2 id="_4-修改表" tabindex="-1">4. 修改表 <a class="header-anchor" href="#_4-修改表" aria-hidden="true">#</a></h2><p>修改表指的是修改数据库中已经存在的数据表的结构。</p><p><strong>使用 ALTER TABLE 语句可以实现：</strong></p><ul><li>向已有的表中添加列</li><li>修改现有表中的列</li><li>删除现有表中的列</li><li>重命名现有表中的列</li></ul><h3 id="_4-1-追加一个列" tabindex="-1">4.1 追加一个列 <a class="header-anchor" href="#_4-1-追加一个列" aria-hidden="true">#</a></h3><p>语法格式如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> 表名 </span><span style="color:#F78C6C;">ADD</span><span style="color:#A6ACCD;"> 【COLUMN】 字段名 字段类型 【</span><span style="color:#F78C6C;">FIRST</span><span style="color:#A6ACCD;">|</span><span style="color:#F78C6C;">AFTER</span><span style="color:#A6ACCD;"> 字段名】;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> dept80 </span></span>
<span class="line"><span style="color:#F78C6C;">ADD</span><span style="color:#A6ACCD;"> job_id </span><span style="color:#F78C6C;">varchar</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">15</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><img src="https://i0.hdslb.com/bfs/album/b45ac69ee2db302ea24561275379bdb1ee0aa907.png" alt=""></p><h3 id="_4-2-修改一个列" tabindex="-1">4.2 修改一个列 <a class="header-anchor" href="#_4-2-修改一个列" aria-hidden="true">#</a></h3><ul><li>可以修改列的数据类型，长度、默认值和位置</li><li>修改字段数据类型、长度、默认值、位置的语法格式如下：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> 表名 </span><span style="color:#F78C6C;">MODIFY</span><span style="color:#A6ACCD;"> 【COLUMN】 字段名1 字段类型 【</span><span style="color:#C792EA;">DEFAULT</span><span style="color:#A6ACCD;"> 默认值】【</span><span style="color:#F78C6C;">FIRST</span><span style="color:#A6ACCD;">|</span><span style="color:#F78C6C;">AFTER</span><span style="color:#A6ACCD;"> 字段名2】;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>举例：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;">	dept80</span></span>
<span class="line"><span style="color:#F78C6C;">MODIFY</span><span style="color:#A6ACCD;"> last_name </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">30</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;">	dept80</span></span>
<span class="line"><span style="color:#F78C6C;">MODIFY</span><span style="color:#A6ACCD;"> salary double(</span><span style="color:#F78C6C;">9</span><span style="color:#A6ACCD;">,</span><span style="color:#F78C6C;">2</span><span style="color:#A6ACCD;">) </span><span style="color:#C792EA;">default</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">1000</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><ul><li>对默认值的修改只影响今后对表的修改</li><li>此外，还可以通过此种方式修改列的约束。这里暂先不讲。</li></ul><h3 id="_4-3-重命名一个列" tabindex="-1">4.3 重命名一个列 <a class="header-anchor" href="#_4-3-重命名一个列" aria-hidden="true">#</a></h3><p>使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> 表名 CHANGE 【column】 列名 新列名 新数据类型;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;">  dept80</span></span>
<span class="line"><span style="color:#A6ACCD;">CHANGE department_name dept_name </span><span style="color:#F78C6C;">varchar</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">15</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_4-4-删除一个列" tabindex="-1">4.4 删除一个列 <a class="header-anchor" href="#_4-4-删除一个列" aria-hidden="true">#</a></h3><p>删除表中某个字段的语法格式如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> 表名 </span><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> 【COLUMN】字段名</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>举例：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;">  dept80</span></span>
<span class="line"><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> COLUMN  job_id; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h2 id="_5-重命名表" tabindex="-1">5. 重命名表 <a class="header-anchor" href="#_5-重命名表" aria-hidden="true">#</a></h2><ul><li>方式一：使用RENAME</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">RENAME </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp</span></span>
<span class="line"><span style="color:#F78C6C;">TO</span><span style="color:#A6ACCD;"> myemp;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><ul><li>方式二：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">table</span><span style="color:#A6ACCD;"> dept</span></span>
<span class="line"><span style="color:#A6ACCD;">RENAME [TO] detail_dept;  </span><span style="color:#676E95;font-style:italic;">-- [TO]可以省略</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><ul><li>必须是对象的拥有者</li></ul><h2 id="_6-删除表" tabindex="-1">6. 删除表 <a class="header-anchor" href="#_6-删除表" aria-hidden="true">#</a></h2><ul><li>在MySQL中，当一张数据表<code>没有与其他任何数据表形成关联关系</code>时，可以将当前数据表直接删除。</li><li>数据和结构都被删除</li><li>所有正在运行的相关事务被提交</li><li>所有相关索引被删除</li><li>语法格式：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><code>IF EXISTS</code>的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。</p><ul><li>举例：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> dept80;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>DROP TABLE 语句不能回滚</li></ul><h2 id="_7-清空表" tabindex="-1">7. 清空表 <a class="header-anchor" href="#_7-清空表" aria-hidden="true">#</a></h2><ul><li>TRUNCATE TABLE语句： <ul><li>删除表中所有的数据</li><li>释放表的存储空间</li></ul></li><li>举例：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">TRUNCATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> detail_dept;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>TRUNCATE语句<strong>不能回滚</strong>，而使用 DELETE 语句删除数据，可以回滚</li><li>对比：</li></ul><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">SET</span><span style="color:#A6ACCD;"> autocommit </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> FALSE;</span></span>
<span class="line"><span style="color:#A6ACCD;">  </span></span>
<span class="line"><span style="color:#F78C6C;">DELETE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> emp2; </span></span>
<span class="line"><span style="color:#A6ACCD;">#</span><span style="color:#F78C6C;">TRUNCATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp2;</span></span>
<span class="line"><span style="color:#A6ACCD;">  </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> emp2;</span></span>
<span class="line"><span style="color:#A6ACCD;">  </span></span>
<span class="line"><span style="color:#F78C6C;">ROLLBACK</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"><span style="color:#A6ACCD;">  </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> emp2;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><blockquote><p>阿里开发规范：</p><p>【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统资源和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。</p><p>说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。</p></blockquote><h2 id="_8-dcl-中-commit-和-rollback" tabindex="-1">8.DCL 中 COMMIT 和 ROLLBACK <a class="header-anchor" href="#_8-dcl-中-commit-和-rollback" aria-hidden="true">#</a></h2><p>COMMIT:提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。</p><p>ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。</p><h2 id="_9-对比-truncate-table-和-delete-from" tabindex="-1">9.对比 TRUNCATE TABLE 和 DELETE FROM <a class="header-anchor" href="#_9-对比-truncate-table-和-delete-from" aria-hidden="true">#</a></h2><p>相同点：都可以实现对表中所有数据的删除，同时保留表结构。</p><p>不同点：</p><p>TRUNCATE TABLE：一旦执行此操作，表数据全部清除。同时，<strong>数据是不可以回滚的</strong>。</p><p>DELETE FROM：一旦执行此操作，表数据可以全部清除（不带WHERE）。<strong>同时，数据是可以实现回滚的</strong>。</p><p>DDL 和 DML 的说明</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">① DDL(这里不单单指truncate，而是指全部的DDL指令)的操作一旦执行，就不可回滚。即使设置指令SET autocommit </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> FALSE，该指令也对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit </span><span style="color:#89DDFF;">=</span><span style="color:#A6ACCD;"> FALSE影响的。)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>② DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 <code>SET autocommit = FALSE</code>，则执行的DML操作就可以实现回滚。</p><h2 id="_10-内容拓展" tabindex="-1">10. 内容拓展 <a class="header-anchor" href="#_10-内容拓展" aria-hidden="true">#</a></h2><h3 id="拓展1：阿里巴巴《java开发手册》之mysql字段命名" tabindex="-1">拓展1：阿里巴巴《Java开发手册》之MySQL字段命名 <a class="header-anchor" href="#拓展1：阿里巴巴《java开发手册》之mysql字段命名" aria-hidden="true">#</a></h3><ul><li>【<code>强制</code>】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。 <ul><li>正例：aliyun_admin，rdc_config，level3_name</li><li>反例：AliyunAdmin，rdcConfig，level_3_name</li></ul></li><li>【<code>强制</code>】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。</li><li>【<code>强制</code>】表必备三字段：id, gmt_create, gmt_modified。 <ul><li>说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新</li></ul></li><li>【<code>推荐</code>】表的命名最好是遵循 “业务名称_表的作用”。 <ul><li>正例：alipay_task 、 force_project、 trade_config</li></ul></li><li>【<code>推荐</code>】库名与应用名称尽量一致。</li><li>【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。 <ul><li>正例：无符号值可以避免误存负数，且扩大了表示范围。</li></ul></li></ul><p><img src="https://i0.hdslb.com/bfs/album/d8e6259b44e122b722b87d6961e21c0fb038fb4f.png" alt=""></p><h3 id="拓展2：如何理解清空表、删除表等操作需谨慎？！" tabindex="-1">拓展2：如何理解清空表、删除表等操作需谨慎？！ <a class="header-anchor" href="#拓展2：如何理解清空表、删除表等操作需谨慎？！" aria-hidden="true">#</a></h3><p><code>表删除</code>操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行<code>备份</code>，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。</p><p>同样的，在使用 <code>ALTER TABLE</code> 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的<code>备份</code>，因为数据库的改变是<code>无法撤销</code>的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。</p><h3 id="拓展3：mysql8新特性—ddl的原子化" tabindex="-1">拓展3：MySQL8新特性—DDL的原子化 <a class="header-anchor" href="#拓展3：mysql8新特性—ddl的原子化" aria-hidden="true">#</a></h3><p>在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即<code>DDL操作要么成功要么回滚</code>。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。</p><p>分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表，结果如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> mytest;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">USE</span><span style="color:#A6ACCD;"> mytest;</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> book1(</span></span>
<span class="line"><span style="color:#A6ACCD;">book_id </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;"> ,</span></span>
<span class="line"><span style="color:#A6ACCD;">book_name </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">255</span><span style="color:#A6ACCD;">)</span></span>
<span class="line"><span style="color:#A6ACCD;">);</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#A6ACCD;">SHOW TABLES;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><p>（1）在MySQL 5.7版本中，测试步骤如下： 删除数据表book1和数据表book2，结果如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">book1</span><span style="color:#A6ACCD;">,book2;</span></span>
<span class="line"><span style="color:#A6ACCD;">ERROR </span><span style="color:#F78C6C;">1051</span><span style="color:#A6ACCD;"> (42S02): Unknown </span><span style="color:#F78C6C;">table</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mytest.book2</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>再次查询数据库中的数据表名称，结果如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> SHOW TABLES;</span></span>
<span class="line"><span style="color:#F78C6C;">Empty</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>从结果可以看出，虽然删除操作时报错了，但是仍然删除了数据表book1。</p><p>（2）在MySQL 8.0版本中，测试步骤如下： 删除数据表book1和数据表book2，结果如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> </span><span style="color:#82AAFF;">book1</span><span style="color:#A6ACCD;">,book2;</span></span>
<span class="line"><span style="color:#A6ACCD;">ERROR </span><span style="color:#F78C6C;">1051</span><span style="color:#A6ACCD;"> (42S02): Unknown </span><span style="color:#F78C6C;">table</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">mytest.book2</span><span style="color:#89DDFF;">&#39;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>再次查询数据库中的数据表名称，结果如下：</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#A6ACCD;">mysql</span><span style="color:#89DDFF;">&gt;</span><span style="color:#A6ACCD;"> show tables;</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| Tables_in_mytest |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------+</span></span>
<span class="line"><span style="color:#A6ACCD;">| book1            |</span></span>
<span class="line"><span style="color:#89DDFF;">+</span><span style="color:#676E95;font-style:italic;">------------------+</span></span>
<span class="line"><span style="color:#F78C6C;">1</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">row</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">in</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">set</span><span style="color:#A6ACCD;"> (</span><span style="color:#F78C6C;">0</span><span style="color:#A6ACCD;">.</span><span style="color:#F78C6C;">00</span><span style="color:#A6ACCD;"> sec)</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>从结果可以看出，数据表book1并没有被删除。</p><h2 id="练习" tabindex="-1">练习 <a class="header-anchor" href="#练习" aria-hidden="true">#</a></h2><p>1.创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">DATABASE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IF</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">NOT</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">EXISTS</span><span style="color:#A6ACCD;"> test01_office </span><span style="color:#F78C6C;">CHARACTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">SET</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">&#39;</span><span style="color:#C3E88D;">utf8</span><span style="color:#89DDFF;">&#39;</span><span style="color:#A6ACCD;">;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>2.创建表dept01</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">/*</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">字段 类型 </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">id INT(7) </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">NAME VARCHAR(25) </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">*/</span></span>
<span class="line"><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> dept01( id </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">7</span><span style="color:#A6ACCD;">), </span><span style="color:#F78C6C;">NAME</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">25</span><span style="color:#A6ACCD;">) );</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>3.将表departments中的数据插入新表dept02中</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> dept02 ASSELECT </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> atguigudb.departments;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>4.创建表emp01</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">/*</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">字段 类型 </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">id INT(7)</span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">first_name VARCHAR (25) </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">last_name VARCHAR(25) </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">dept_id INT(7) </span></span>
<span class="line"><span style="color:#676E95;font-style:italic;">*/</span></span>
<span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp01( </span></span>
<span class="line"><span style="color:#A6ACCD;">    id </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">7</span><span style="color:#A6ACCD;">),</span></span>
<span class="line"><span style="color:#A6ACCD;">    first_name </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">25</span><span style="color:#A6ACCD;">), </span></span>
<span class="line"><span style="color:#A6ACCD;">    last_name </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">25</span><span style="color:#A6ACCD;">), </span></span>
<span class="line"><span style="color:#A6ACCD;">    dept_id </span><span style="color:#F78C6C;">INT</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">7</span><span style="color:#A6ACCD;">) </span></span>
<span class="line"><span style="color:#A6ACCD;">);</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div><p>5.将列last_name的长度增加到50</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp01 </span><span style="color:#F78C6C;">MODIFY</span><span style="color:#A6ACCD;"> last_name </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">50</span><span style="color:#A6ACCD;">);</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>6.根据表employees创建emp02</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">CREATE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp02 </span></span>
<span class="line"><span style="color:#F78C6C;">AS</span><span style="color:#A6ACCD;"> </span></span>
<span class="line"><span style="color:#F78C6C;">SELECT</span><span style="color:#A6ACCD;"> </span><span style="color:#89DDFF;">*</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">FROM</span><span style="color:#A6ACCD;"> atguigudb.</span><span style="color:#89DDFF;">`</span><span style="color:#C3E88D;">employees</span><span style="color:#89DDFF;">`</span><span style="color:#A6ACCD;">; </span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>7.删除表emp01</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">IF</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">EXISTS</span><span style="color:#A6ACCD;"> emp01;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>8.将表emp02重命名为emp01</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#676E95;font-style:italic;">-- ALTER TABLE emp02 RENAME TO emp01; </span></span>
<span class="line"><span style="color:#A6ACCD;">RENAME </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp02 </span><span style="color:#F78C6C;">TO</span><span style="color:#A6ACCD;"> emp01;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>9.在表dept02和emp01中添加新列test_column，并检查所作的操作</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp01 </span><span style="color:#F78C6C;">ADD</span><span style="color:#A6ACCD;"> test_column </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">10</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">DESC</span><span style="color:#A6ACCD;"> emp01; </span></span>
<span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> dept02 </span><span style="color:#F78C6C;">ADD</span><span style="color:#A6ACCD;"> test_column </span><span style="color:#F78C6C;">VARCHAR</span><span style="color:#A6ACCD;">(</span><span style="color:#F78C6C;">10</span><span style="color:#A6ACCD;">); </span></span>
<span class="line"><span style="color:#F78C6C;">DESC</span><span style="color:#A6ACCD;"> dept02;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>10.直接删除表emp01中的列 department_id</p><div class="language-sql line-numbers-mode"><pre><code><span class="line"><span style="color:#F78C6C;">ALTER</span><span style="color:#A6ACCD;"> </span><span style="color:#F78C6C;">TABLE</span><span style="color:#A6ACCD;"> emp01 </span><span style="color:#F78C6C;">DROP</span><span style="color:#A6ACCD;"> COLUMN department_id;</span></span>
<span class="line"></span></code></pre><div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div></div></div><!----></main><!--[--><!--]--><footer class="VPContentDocFooter" data-v-4fe9b7bd data-v-7f892bef><a class="prev-link" href="/mysql/09.html" data-v-7f892bef><span class="desc" data-v-7f892bef><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-link-icon" data-v-7f892bef><path d="M15,19c-0.3,0-0.5-0.1-0.7-0.3l-6-6c-0.4-0.4-0.4-1,0-1.4l6-6c0.4-0.4,1-0.4,1.4,0s0.4,1,0,1.4L10.4,12l5.3,5.3c0.4,0.4,0.4,1,0,1.4C15.5,18.9,15.3,19,15,19z"></path></svg> Previous</span><span class="title" data-v-7f892bef>09 【子查询】</span></a><a class="next-link" href="/mysql/11.html" data-v-7f892bef><span class="desc" data-v-7f892bef>Next <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" viewbox="0 0 24 24" class="vt-link-icon" data-v-7f892bef><path d="M9,19c-0.3,0-0.5-0.1-0.7-0.3c-0.4-0.4-0.4-1,0-1.4l5.3-5.3L8.3,6.7c-0.4-0.4-0.4-1,0-1.4s1-0.4,1.4,0l6,6c0.4,0.4,0.4,1,0,1.4l-6,6C9.5,18.9,9.3,19,9,19z"></path></svg></span><span class="title" data-v-7f892bef>11 【数据处理之增删改】</span></a></footer></div></div></div></div><div class="visually-hidden" aria-live="polite" data-v-23306c50>10 【创建和管理表】 has loaded</div></div></div>
    <script>__VP_HASH_MAP__ = JSON.parse("{\"about_canshu.md\":\"5cbc0865\",\"about_chahuo.md\":\"4437b8d9\",\"about_dayin.md\":\"34b5b775\",\"ajax_01.md\":\"2bc3ac36\",\"ajax_02.md\":\"2b66aa43\",\"echarts_01.md\":\"8784be9b\",\"echarts_02.md\":\"3e6ecdea\",\"echarts_03.md\":\"28ca81f0\",\"echarts_04.md\":\"d02d74fb\",\"echarts_05.md\":\"f150b054\",\"es6_01.md\":\"53b72f76\",\"es6_02.md\":\"1fc3a71b\",\"es6_03.md\":\"2b8d77d0\",\"es6_04.md\":\"19f87206\",\"es6_05.md\":\"78caf034\",\"es6_06.md\":\"f8d3c991\",\"es6_07.md\":\"324f9949\",\"es6_08.md\":\"87411498\",\"es6_09.md\":\"3486bc74\",\"es6_10.md\":\"71a60a12\",\"es6_11.md\":\"6d589e6c\",\"es6_12.md\":\"d82e096b\",\"es6_13.md\":\"78856900\",\"es6_14.md\":\"40c129d1\",\"git_01.md\":\"bf73bb28\",\"git_02.md\":\"650fa8d0\",\"git_03.md\":\"4384f718\",\"git_04.md\":\"14478c6d\",\"index.md\":\"09348a64\",\"jihe_react_index.md\":\"f57878ae\",\"jihe_reactnative_index.md\":\"c7e0faf3\",\"jihe_vue_index.md\":\"990e53d2\",\"jihe_vuerouter_index.md\":\"d28167d1\",\"jihe_vuex_index.md\":\"b9d762ab\",\"js_01.md\":\"48c6f3a8\",\"js_02.md\":\"a5183efb\",\"js_03.md\":\"5a8ce924\",\"js_04.md\":\"3db3e706\",\"js_05.md\":\"7b8951d8\",\"js_06.md\":\"dc3d579a\",\"js_07.md\":\"e7a4eda3\",\"js_08.md\":\"f75c92a3\",\"js_09.md\":\"80e86e46\",\"js_10.md\":\"646ac118\",\"js_11.md\":\"7d754050\",\"js_12.md\":\"7e6eae06\",\"js_13.md\":\"947200bc\",\"js_14.md\":\"1c0566af\",\"js_15.md\":\"d465d165\",\"js_16.md\":\"1c7c9370\",\"js_17.md\":\"71dec812\",\"js_18.md\":\"d68348c2\",\"js_19.md\":\"72c34e28\",\"js_20.md\":\"ff30fdd3\",\"js_21.md\":\"aa9bea2b\",\"js_22.md\":\"6bc45920\",\"js_23.md\":\"3131d6e6\",\"js_24.md\":\"5f12ad69\",\"js_25.md\":\"c5932cf5\",\"js_26.md\":\"b8e719df\",\"js_27.md\":\"6679728d\",\"js_28.md\":\"c9ec23e0\",\"js_29.md\":\"09376a4e\",\"js_30.md\":\"dbc798cb\",\"js_31.md\":\"283dae59\",\"js_32.md\":\"11d6ad8d\",\"js_33.md\":\"e4232cd9\",\"js_34.md\":\"a3b52a45\",\"js_35.md\":\"1477beb5\",\"js_36.md\":\"01702bb7\",\"keshitong_caigou.md\":\"c5f9edad\",\"keshitong_caiwu.md\":\"90dbd259\",\"keshitong_index.md\":\"7522f84e\",\"keshitong_xiaoshou.md\":\"b4d67695\",\"keshitong_xunjia.md\":\"49b056fe\",\"less_01.md\":\"cc6e26b8\",\"linux_01.md\":\"40a56533\",\"linux_02.md\":\"fa080f7e\",\"linux_03.md\":\"9c723c17\",\"linux_04.md\":\"c4b01f09\",\"linux_05.md\":\"cfe63746\",\"linux_06.md\":\"4e05c7c6\",\"linux_07.md\":\"2dbdda0c\",\"linux_08.md\":\"80f78478\",\"linux_09.md\":\"b9157005\",\"linux_10.md\":\"55e60bfe\",\"linux_11.md\":\"3b59a057\",\"mongodb_01.md\":\"4e9aab55\",\"mysql_01.md\":\"66f81032\",\"mysql_02.md\":\"38ff9d8a\",\"mysql_03.md\":\"4a1047e3\",\"mysql_04.md\":\"ed6dc0f6\",\"mysql_05.md\":\"fb50b511\",\"mysql_06.md\":\"4f4b1170\",\"mysql_07.md\":\"3f58b4d2\",\"mysql_08.md\":\"7e23f325\",\"mysql_09.md\":\"bef84df5\",\"mysql_10.md\":\"33e4e499\",\"mysql_11.md\":\"4e732a7d\",\"nodejs_01.md\":\"614d43a3\",\"nodejs_02.md\":\"4899f883\",\"nodejs_03.md\":\"f47d5f16\",\"nodejs_04.md\":\"7755e031\",\"nodejs_05.md\":\"ef947c59\",\"nodejs_06.md\":\"ab0a9b5a\",\"nodejs_07.md\":\"ad0e80be\",\"nodejs_08.md\":\"11027bdf\",\"nodejs_09.md\":\"4e4f9b03\",\"nodejs_10.md\":\"c4453555\",\"nodejs_11.md\":\"9f62715b\",\"nodejs_12.md\":\"1229399a\",\"nodejs_13.md\":\"e490f00c\",\"nodejs_14.md\":\"d756be55\",\"nodejs_15.md\":\"eb622fe8\",\"nodejs_16.md\":\"a5beb6c6\",\"sass_01.md\":\"0d150b1a\",\"sass_02.md\":\"8ee85f45\",\"sass_03.md\":\"7c4069d6\",\"sass_04.md\":\"85602152\",\"sass_05.md\":\"b62c38b1\",\"sass_06.md\":\"d02e3012\",\"sass_07.md\":\"edcc3b93\",\"sass_08.md\":\"9c108ef4\",\"sass_09.md\":\"3bdde859\",\"sass_10.md\":\"7eac5fed\",\"sass_11.md\":\"3d1861cf\",\"tailwindcss_01.md\":\"de90a25d\",\"tailwindcss_02.md\":\"bbfd989e\",\"tailwindcss_03.md\":\"dc351bec\",\"tailwindcss_04.md\":\"27601329\",\"test_index.md\":\"57ee7ee1\",\"test_muban.md\":\"5118e1bd\",\"test_zhongqiu.md\":\"3d9111e0\",\"typescript_01.md\":\"27d9f7e0\",\"typescript_02.md\":\"6cf9cb32\",\"typescript_03.md\":\"a851d1d2\",\"typescript_04.md\":\"954cb0d0\",\"typescript_05.md\":\"989bd8d1\",\"typescript_06.md\":\"2c90dcac\",\"vue2_01.md\":\"fd17f4d6\",\"vue2_02.md\":\"84ab761d\",\"vue2_03.md\":\"a64353d4\",\"vue2_04.md\":\"b9b0b23e\",\"vue2_05.md\":\"81a8a08d\",\"vue2_06.md\":\"2ce32e2a\",\"vue2_07.md\":\"19b50842\",\"vue2_08.md\":\"4c8868f5\",\"vue2_09.md\":\"94977e11\",\"vue2_10.md\":\"8e789ccc\",\"vue2_11.md\":\"ee9e7943\",\"vue2_12.md\":\"5249d4c5\",\"vue2_13.md\":\"048d4471\",\"vue2_14.md\":\"63be0ece\",\"vue2_15.md\":\"bd679101\",\"vue2_16.md\":\"bbe6e777\",\"vue3_01.md\":\"27d0d58b\",\"vue3_02.md\":\"22bcb9e5\",\"vue3_03.md\":\"3c52430b\",\"vue3_04.md\":\"0bb848ba\",\"vue3_06.md\":\"1cc04cca\",\"vue3_07.md\":\"28531efb\",\"vue3_08.md\":\"66ee6b02\",\"vue3_09.md\":\"9ea9285a\",\"vue3_10.md\":\"a1d6a99a\",\"vue3_11.md\":\"cb4aa0f5\",\"vue3_12.md\":\"27e13ddf\",\"vue3_13.md\":\"3976cde5\",\"vue3_14.md\":\"1b2e16e7\",\"vue3_15.md\":\"24dc5c2d\",\"vue3_16.md\":\"5efc1ed6\",\"vue3_17.md\":\"ac67e2ed\",\"yuanshen_index.md\":\"437ce691\"}")</script>
    <script type="module" async src="/assets/app.82d46cfc.js"></script>
    
  </body>
</html>